package com.twang.stock.controller.old;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.google.common.collect.Lists;
import com.twang.stock.dto.BaseRequest;
import com.twang.stock.entity.CompanyCodeInfo;
import com.twang.stock.entity.StockFinanceReport;
import com.twang.stock.mapper.CompanyCodeInfoMapper;
import com.twang.stock.service.jinyan.CompanyInfoService;
import com.twang.stock.service.jinyan.HolidayService;
import com.twang.stock.service.jinyan.StockFinanceReportService;
import com.twang.stock.utils.date.HolidayCopy;
import com.twang.stock.utils.excel.EasyExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Controller
@RequestMapping(value = "/company")
@Slf4j
public class CompanyInfoController {

    @Resource
    private CompanyCodeInfoMapper companyCodeInfoMapper;

    @Autowired
    private HolidayService holidayService;

    @Autowired
    private CompanyInfoService companyInfoService;

    @Autowired
    private StockFinanceReportService stockFinanceReportService;

    private static String qequest_update="update";

    private static String qequest_desc="desc";

    private static String qequest_asc="asc";

    private static String qequest_moneyIn="moneyIn";

    private static String qequest_moneyInDays="moneyInDays";
    /**
     * 获取数据
     *HTTP http://127.0.0.1:8080/stockInfo/query
     * @return
     */
    @RequestMapping(value = "/query")
    public ModelAndView query(BaseRequest request) {

        ModelAndView modelAndView=new ModelAndView();
        modelAndView.addObject("resultList", companyInfoService.query());
        modelAndView.setViewName("companyInfo");
        return  modelAndView;
    }

    @RequestMapping(value = "/markType")
    public ModelAndView markType(BaseRequest request) throws Exception {
        companyInfoService.setType(request);
        ModelAndView mv = new ModelAndView("redirect:/stockInfo/query");
        return mv;
    }

    /**
     *生成当日初选数据
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/granteTodayStockInfo")
    public void granteTodayStockInfo(BaseRequest request,HttpServletResponse response) throws Exception {
        List<String> stockList = Lists.newArrayList();
        if (qequest_asc.equals(request.getFlag())) {
            stockList = companyInfoService.granteTodayStockInfo().stream().map(CompanyCodeInfo::getCompanyCode).collect(Collectors.toList());
        }
        if (qequest_desc.equals(request.getFlag())) {
            stockList = companyInfoService.granteTodayStockDesc().stream().map(CompanyCodeInfo::getCompanyCode).collect(Collectors.toList());
        }
        if (qequest_moneyIn.equals(request.getFlag())) {
            stockList = companyInfoService.granteTodayStockMoneyIn50().stream().map(CompanyCodeInfo::getCompanyCode).collect(Collectors.toList());
        }
        if (qequest_moneyInDays.equals(request.getFlag())) {
            stockList = companyInfoService.granteTodayStockMoneyInDays().stream().map(CompanyCodeInfo::getCompanyCode).collect(Collectors.toList());
        }
        List<String> temp = Lists.newArrayList();
        stockList.forEach(item -> {
            item = item.substring(2, 8);
            temp.add(item);
        });
        writeToResource(temp,request, response);
    }

    /**
     * 写到资源文件
     *
     * @param stockLists
     */
    private static void writeToResource(List<String> stockLists,BaseRequest request, HttpServletResponse response) throws IOException {
        // 保证目录一定存在
        StringBuffer buffer = new StringBuffer();
        for (String student : stockLists) {
            buffer.append(student);
            buffer.append("\r\n");
        }
        String fileSuffixName ="txt";
        response.reset(); //清除缓存
        response.setContentType("application/" + fileSuffixName + ";" + "charset = UTF-8"); //设置字符集和文件后缀名
        String name="";
        if (qequest_asc.equals(request.getFlag())) {
             name="今日涨幅排行_" + HolidayCopy.getToday();
        }
        if (qequest_desc.equals(request.getFlag())) {
            name="涨幅排行_" + HolidayCopy.getToday();

        }
        if (qequest_moneyIn.equals(request.getFlag())) {
            name="资金流入排行_" + HolidayCopy.getToday();
        }
        if (qequest_moneyInDays.equals(request.getFlag())) {
            name="资金连续流入排行_" + HolidayCopy.getToday();
        }
        name = new String(name.getBytes(), "ISO-8859-1");
        response.setHeader("Content-Disposition", "attachment; filename=" + name + "." + fileSuffixName); // 设置文件名称
        OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
        toClient.write(String.valueOf(buffer).getBytes());
        toClient.flush();
        toClient.close();
    }

    /**
     * 最新版本导出
     *  http://127.0.0.1:8080/company/export
     */
    @RequestMapping(value = "/export")
    public void exportReport(BaseRequest request,HttpServletResponse response) throws Exception {
        List<CompanyCodeInfo> stockList=Lists.newArrayList();
        //
         stockList = companyCodeInfoMapper.getStockInfoMoreThan5ForReport(holidayService.getDayByNum(0));

//         List<StockFinanceReport> aaa=stockFinanceReportMapper.queryALl();
//         for(StockFinanceReport stockFinanceReport:aaa){
//             CompanyCodeInfo companyCodeInfo=new CompanyCodeInfo();
//             companyCodeInfo.setCompanyCode(stockFinanceReport.getStockCode());
//             companyCodeInfo.setShortName(stockFinanceReport.getStockAlias());
//             stockList.add(companyCodeInfo);
//         }
        //查询板块信息
        excelExport(stockList,response,new HashMap<>());
//        int row=0;
//        for(CompanyCodeInfo companyCodeInfo:stockList){
//            String code=companyCodeInfo.getCompanyCode().replace("sh","").replace("sz","");
//            //stockFinanceReportService.deletedByCode(code);
//            stockFinanceReportService.excuteRepaotqueryByCode(code,companyCodeInfo.getShortName());
//            //查询年报
//            List<StockFinanceReport> reportList=stockFinanceReportService.queryByCodeDesc(code);
//
//        }
    }

    private void excelExport( List<CompanyCodeInfo> stockList, HttpServletResponse response, Map<String, Object> data) throws Exception {
        // 结算对账单”+“业务主体名称/客户名称”出库申请单号
        String fileName = "今日报表_" + HolidayCopy.getRealToday();
        data.put("fileName",fileName);
        TemplateExportParams param = new TemplateExportParams(EasyExcelUtil.convertTemplatePath("templates/excel/report.xlsx"), true);
        //导出list集合
        try {
            Workbook book = ExcelExportUtil.exportExcel(param, data);
            //获取第一个工作表
            Sheet sheet = book.getSheetAt(0);
            int rowNumber =0;
            Cell cell;
            Row row;
            for (int i = 0; i < stockList.size(); i++) {
                CompanyCodeInfo companyCodeInfo= stockList.get(i);
                //
                try{
                    stockFinanceReportService.excuteRepaotqueryByCode(companyCodeInfo.getCompanyCode().replace("sh","").replace("sz",""),companyCodeInfo.getShortName());
                }catch (Exception e){
                    log.error("更新财报出错 {},{}",companyCodeInfo.getShortName(),e);
                }
                //
                row = createRow(sheet, rowNumber);
                //1、股票Code
                cell = row.createCell(0);
                cell.setCellValue(companyCodeInfo.getCompanyCode());
                setStyle(book, cell.getCellStyle());
                //2、股票名称
                cell = row.createCell(1);
                cell.setCellValue(companyCodeInfo.getShortName());
                setStyle(book, cell.getCellStyle());
                //2、股票名称
                cell = row.createCell(2);
                cell.setCellValue("财报");
                setStyle(book, cell.getCellStyle());
                //3、年份
                cell = row.createCell(3);
                cell.setCellValue("年份");
                setStyle(book, cell.getCellStyle());
                //4、财报类型
                cell = row.createCell(4);
                cell.setCellValue("财报类型");
                setStyle(book, cell.getCellStyle());
                //5、净利润
                cell = row.createCell(5);
                cell.setCellValue("净利润");
                setStyle(book, cell.getCellStyle());
                //6、环比增长率
                cell = row.createCell(6);
                cell.setCellValue("环比增长率");
                setStyle(book, cell.getCellStyle());
                //7、同比增长率
                cell = row.createCell(7);
                cell.setCellValue("同比增长率");
                setStyle(book, cell.getCellStyle());
                rowNumber++;
                //查询年报
                List<StockFinanceReport> reportList=stockFinanceReportService.queryByCodeDesc(companyCodeInfo.getCompanyCode().replace("sz","").replace("sh",""));

                for(StockFinanceReport stockFinanceReport:reportList) {
                    row = createRow(sheet, rowNumber);
//                    //2、股票名称
//                    cell = row.createCell(2);
//                    cell.setCellValue("财报");
//                    setStyle(book, cell.getCellStyle());
                    //3、年份
                    cell = row.createCell(3);
                    cell.setCellValue(stockFinanceReport.getYear());
                    setStyle(book, cell.getCellStyle());
                    //4、财报类型
                    cell = row.createCell(4);
                    cell.setCellValue(stockFinanceReport.getQuarter());
                    setStyle(book, cell.getCellStyle());
                    //5、净利润
                    cell = row.createCell(5);
                    cell.setCellValue(setJLR(stockFinanceReport.getJlr(),stockFinanceReport.getJlrDw())+"");
                    setStyle(book, cell.getCellStyle());
                    //6、环比增长率
                    cell = row.createCell(6);
                    cell.setCellValue(getJlrHbzzl(stockFinanceReport.getJlrYysrhbzz()));
                    setStyle(book, cell.getCellStyle());
                    collor(stockFinanceReport.getJlrYysrhbzz(),stockFinanceReport.getJlrRise(),cell.getCellStyle());
                    //7、同比增长率
                    cell = row.createCell(7);
                    cell.setCellValue(stockFinanceReport.getJlrRise()+"");
                    setStyle(book, cell.getCellStyle());
                    collor(stockFinanceReport.getJlrYysrhbzz(),stockFinanceReport.getJlrRise(),cell.getCellStyle());
                    //
                    rowNumber++;
                }
                if(reportList.size()>=5){
                    sheet.addMergedRegion(new CellRangeAddress(rowNumber-7,rowNumber-1, 2, 2 ));
                }
                rowNumber++;
                //-------------------------------------------------
            }
//            //删除
//           removeRow(sheet, 0);
//            //合并第0到第6列
//           int firstRow = 14 + stockList.size() - 1;
//           sheet.addMergedRegion(new CellRangeAddress(firstRow, firstRow + 10, 0, 6));
            //强制重载公式
            book.setForceFormulaRecalculation(true);
            //下载方法
            export(response, book, fileName);

        } catch (Exception e) {
            log.error("导出模板Excel，失败:" + e);
            throw e;
        }
    }

    private String setJLR(BigDecimal jlr,Integer jlrDW) {
        if (null != jlr) {
            if (jlrDW.intValue() == 1) {
                return jlr.toString() + "亿";
            }
            if (jlrDW.intValue() == 2) {
                return jlr.toString() + "万";
            }
        } else {
            return jlr.toString();
        }
        return "-";
    }

    private String getJlrHbzzl(BigDecimal jlrHbzzl){
        if(null!=jlrHbzzl){
            if(jlrHbzzl.compareTo(BigDecimal.ZERO)==1){
                if(jlrHbzzl.compareTo(BigDecimal.ONE)==0){
                    return "扭亏";
                }else{
                    return jlrHbzzl.toString();
                }
            }
            if(jlrHbzzl.compareTo(BigDecimal.ZERO)==-1){
                if(jlrHbzzl.compareTo(new BigDecimal(-1))==0){
                    return "亏损";
                }else{
                    return jlrHbzzl.toString();
                }
            }
        }else {
            return "-";
        }
        return "-";
    }

    private void collor(BigDecimal jlrHbzzl,BigDecimal getJlrRise,CellStyle cellStyle ) {
        if (null != jlrHbzzl && null != getJlrRise) {
            if (jlrHbzzl.compareTo(new BigDecimal(100)) == 1
                    && getJlrRise.compareTo(new BigDecimal(100)) == 1) {
                cellStyle.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
            }
        }
    }

    /**
     * export导出请求头设置
     *
     * @param response
     * @param workbook
     * @param fileName
     * @throws Exception
     */
    private static void export(HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
        //生成excel文件流
        ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
        ServletOutputStream out = response.getOutputStream();
        workbook.write(byteOut);
        // 设置文件名
        fileName = fileName.concat(".xlsx");
        String filename = URLEncoder.encode(fileName, "UTF-8");
        byte[] content = byteOut.toByteArray();
        InputStream in = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();
        //        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setContentType("application/octet-stream");
        // response.setContentType("application/x-msdownload");
        response.setHeader("Content-Disposition", "attachment;filename=" + filename);
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, PATCH, DELETE");
        response.setHeader("Access-Control-Max-Age", "3600");
        response.setHeader("Access-Control-Allow-Headers", "Content-Type, Authorization, Accept, X-Requested-With");
//        //客户端下载
//        EasyExcelUtil.writeToClient(in, out);
        OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
        toClient.write(content);
        toClient.flush();
        toClient.close();
    }


    public static void removeRow(Sheet sheet, int rowIndex) {
        int lastRowNum = sheet.getLastRowNum();
        if (rowIndex >= 0 && rowIndex < lastRowNum)
            sheet.shiftRows(rowIndex + 1, lastRowNum, -1, true, false); // 将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
        if (rowIndex == lastRowNum) {
            Row removingRow = sheet.getRow(rowIndex);
            if (removingRow != null)
                sheet.removeRow(removingRow);
        }
    }

    /**
     * 找到需要插入的行数，并新建一个POI的row对象
     *
     * @param sheet
     * @param rowIndex
     * @return
     */
    private Row createRow(Sheet sheet, Integer rowIndex) {
        Row row = null;
        if (sheet.getRow(rowIndex) != null) {
            int lastRowNo = sheet.getLastRowNum();
            sheet.shiftRows(rowIndex, lastRowNo, 1);
        }
        row = sheet.createRow(rowIndex);
        //获取当前行
        Row rowSource = sheet.getRow(rowIndex);
        //获取当前行样式
        CellStyle rowStyle = rowSource.getRowStyle();
        if (rowStyle != null) {
            row.setRowStyle(rowStyle);
            row.setHeight(rowSource.getHeight());
        }
//        for (int col = 0; col < 11; col++) {
//            Cell cellSource = rowSource.getCell(col);
//            Cell cellInsert = row.createCell(col);
//            CellStyle cellStyle = cellSource.getCellStyle();
//            //设置单元格样式
//            if (cellStyle != null) {
//                cellInsert.setCellStyle(cellStyle);
//            }
//        }
        return row;
    }
    private void setStyle(Workbook workbook, CellStyle cellStyle) {
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //
        cellStyle.setFont(getFont(workbook, (short) 10, false));
    }

    /**
     * 字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName("微软雅黑");
        //是否加粗
        font.setBold(isBold);
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
    }

}
